package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.DeliveryAddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl implements DeliveryAddressDao {
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;
    //根据用户编号查询所属送货地址
    @Override
    public List<DeliveryAddress> queryById(String userId) throws SQLException {
        conn = DBUtil.getConnection();
        String sql = "select address from deliveryaddress where userId = ?";
        pst = conn.prepareStatement(sql);
        pst.setString(1,userId);
        rs = pst.executeQuery();
        List<DeliveryAddress> list = new ArrayList<>();
        try {
            while (rs.next()){
                DeliveryAddress deliveryAddress = new DeliveryAddress();
                deliveryAddress.setAddress(rs.getString("address"));
                list.add(deliveryAddress);
            }
            return list;
        }finally {
            DBUtil.close(rs,pst);
        }
    }
//根据送货地址编号查询送货地址
    @Override
    public DeliveryAddress queryDABbId(Integer daId) throws SQLException {
        conn = DBUtil.getConnection();
        String sql = "select address from deliveryaddress where daId = ?";
        pst = conn.prepareStatement(sql);
        pst.setInt(1,daId);
        rs = pst.executeQuery();
        try {
            while (rs.next()){
                DeliveryAddress deliveryAddress = new DeliveryAddress();
                deliveryAddress.setAddress(rs.getString("address"));
                return deliveryAddress;
            }
        }finally {
            DBUtil.close(rs,pst);
        }
        return null;
    }
//向送货地址表中添加一条记录
    @Override
    public int insert(String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        try {
            conn = DBUtil.getConnection();
            String sql = "insert into deliveryaddress (contactName,contactSex,contactTel,address,userId) values (?,?,?,?,?)";
            pst = conn.prepareStatement(sql);
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            int a = pst.executeUpdate();
            return a;
        }finally {
            DBUtil.close(rs,pst);
        }
    }
//根据送货地址编号更新送货地址信息
    @Override
    public int update(DeliveryAddress deliveryAddress) throws SQLException {
        try {
            conn = DBUtil.getConnection();
            String sql = "update deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=?,userId=? where daId = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,deliveryAddress.getContactName());
            pst.setInt(2,deliveryAddress.getContactSex());
            pst.setString(3,deliveryAddress.getContactTel());
            pst.setString(4,deliveryAddress.getAddress());
            pst.setString(5,deliveryAddress.getUserId());
            pst.setInt(6,deliveryAddress.getDaId());
            int a = pst.executeUpdate();
            return a;
        }finally {
            DBUtil.close(rs,pst);
        }
    }
//根据送货地址编号删除一条记录
    @Override
    public int delete(Integer daId) throws SQLException {
        try{
            conn = DBUtil.getConnection();
            String sql = "delete from deliveryaddress where daId = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,daId);
            int a = pst.executeUpdate();
            return a;
        }finally {
            DBUtil.close(rs,pst);
        }
    }
}
